package org.iteam.xlz.core.service.report;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.iteam.core.utils.DateUtil;
import org.iteam.db.BaseService;
import org.iteam.xlz.core.entity.report.AppReport;

public class AppReportService extends BaseService<AppReport>{
	
	private static AppReportService instance;
	
	private AppReportService(){}
	
	public static AppReportService getInstance(){
		if(null==instance){
			instance=new AppReportService();
		}
		return instance;
	}
	
	public long counts(Date first,Date last){
		if(null==first||null==last){
			Calendar c=Calendar.getInstance();
			return c.get(Calendar.DAY_OF_MONTH);
		}else{
			long i=last.getTime()-first.getTime();
			if(i>0){
				return i/24/3600/1000+1;
			}else{
				return 0;
			}
		}
	}
	
	//倒叙排列结果
	public List<AppReport> query(Date l,Date r,int page,int rows,boolean limit){
		List<AppReport> list=new ArrayList<AppReport>();
		
		Calendar last=Calendar.getInstance();//终止时间
		Calendar first=Calendar.getInstance();//起始时间
		Date now=last.getTime();
		if(null==l||null==r){
			first.set(Calendar.DAY_OF_MONTH,1);
		}else{
			first.setTime(l);
			last.setTime(r);
			if(now.getTime()<r.getTime()){
				last.setTime(now);//过滤掉查询"今天以后"时间段的请求
			}
			
			if(now.getTime()<l.getTime()){
				first.setTime(now);//过滤掉查询"今天以后"时间段的请求
			}
		}
		if(limit){
			last.add(Calendar.DAY_OF_MONTH,-(page-1)*rows);//终止天号
			first.setTime(last.getTime());
			first.add(Calendar.DAY_OF_MONTH,1-rows);//起始天号
			if(null==l||null==r){
				if(first.get(Calendar.MONTH)!=last.get(Calendar.MONTH)){
					first.setTime(last.getTime());
					first.set(Calendar.DAY_OF_MONTH,1);//只查询到本月1号
				}
			}else{
				if(first.getTime().getTime()<l.getTime()){
					first.setTime(l);//最多只到起始时间
				}
			}
		}

		Connection con=null;
		try {
			con=super.getConnection(true);
			while(true){
				if(first.after(last)){
					break;
				}
				String month=(last.get(Calendar.MONTH)+1)+"";
				String day=last.get(Calendar.DAY_OF_MONTH)+"";
				month=(month.length()==1)?("0"+month):month;
				day=(day.length()==1)?("0"+day):day;
				String type1=last.get(Calendar.YEAR)+"/"+month+"/"+day,
						type2=last.get(Calendar.YEAR)+"-"+month+"-"+day;
				//从type2开始上推30天
				Calendar temp=Calendar.getInstance();
				temp.setTime(last.getTime());
				temp.add(Calendar.DAY_OF_MONTH,-30);
				String firstDay=DateUtil.formatDate(temp.getTime());
				StringBuilder sql=new StringBuilder("select '"+type1+"' as time,");
				sql.append("(select count(1) from userinfo where createtime like '%"+type2+"%' and os=0) as androidNew,");
				sql.append("(select count(1) from userinfo where createtime like '%"+type2+"%' and os=1) as iosNew,");
				sql.append("(select count(1) from (select count(uid) from loguser where createtime like '%"+type2+"%' group by uid) v) as dayUser,");
				sql.append("(select count(1) from (select count(uid) from loguser where createtime between '"+firstDay+"' and '"+type2+"' group by uid) v) as monthUser,");
				sql.append("(select count(1) from userinfo where os=0) as androidTotal,");
				sql.append("(select count(1) from userinfo where os=1) as iosTotal from dual");
				list.add(super.show(AppReport.class,con,sql.toString()));
				
				last.add(Calendar.DAY_OF_MONTH,-1);//减一天
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		}finally{
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}